# coding=utf-8
"""
@author : leecrest
@time   : 2014/6/18 8:09
@brief  : 从Excel文件中导出数值表，保存成json文件

Excel文件格式如下：请务必遵循
1、数据区中，第一行是中文表头，第二行是英文表头，第三行是数据类型，第四行开始才是具体数值

checker编写规则：
1、文件名是类型名称，必须包含一个函数 Check，用于检查数据是否合格，并将合格数据最终转化成指定格式的数据返回

"""

import sys
import os
import json
import xlrd

TYPE_SPLIT = "@"

if not "g_TypeChecker" in globals():
	g_Checker = {}
	g_Maker = {}


#根据配置文件，依次读取xlsx文件，并转换
def Export(sConfigFile):
	f = open(sConfigFile, "r")
	dConfig = json.load(f)
	f.close()

	sRoot = dConfig.get("root", "./")
	sOut = dConfig.get("out", "./")

	print u"导出路径：", sRoot
	dFile = dConfig["file"]
	for sFile, dSheetList in dFile.iteritems():
		sFull = os.path.join(sRoot, sFile)
		print u"开始导出 ..", sFull
		if not os.path.exists(sFull) or not os.path.isfile(sFull):
			print u"文件错误：%s" % sFull
			continue
		oFile = OpenExcel(sFull)
		if not oFile:
			return
		if not os.path.exists(sOut):
			os.makedirs(sOut)
		for dSheetCfg in dSheetList:
			sSheet = dSheetCfg["name"]
			print u"\t 导出 [%s]" % sSheet,
			iLeft   = dSheetCfg.get("left", 0)
			iRight  = dSheetCfg.get("right", 0)
			iTop    = dSheetCfg.get("top", 0)
			iBottom = dSheetCfg.get("bottom", 0)
			if (iLeft < 0 or iRight < 0 or iTop < 0 or iBottom < 0 or
				    (iRight > 0 and iLeft > iRight) or
				    (iBottom > 0 and iTop > iBottom)):
				print u"配置错误(%s %s)" % (sFile, sSheet)
				continue
			oSheet = oFile.sheet_by_name(sSheet)
			if not oSheet:
				continue
			sOutSheet = os.path.join(sOut, dSheetCfg.get("out",sSheet))
			if ExportSheet(sOutSheet, oSheet, dSheetCfg["maker"], iLeft, iRight, iTop, iBottom):
				print u" ... 成功"
			else:
				print u" ... 失败"
				return

#将xlsx文件载入内存
def OpenExcel(sFile):
	try:
		data = xlrd.open_workbook(sFile)
		return data
	except Exception, e:
		raise str(e)

#解析某个xlsx中的某个Sheet
def ExportSheet(sOutFile, oSheet, sMaker, iLeft, iRight, iTop, iBottom):
	if (iLeft >= oSheet.ncols or iRight > oSheet.ncols or
		iTop >= oSheet.nrows or iBottom > oSheet.nrows):
		return False
	if iRight == 0:
		iRight = oSheet.ncols
	if iBottom == 0:
		iBottom = oSheet.nrows

	sCnList = oSheet.row_values(iTop)       #中文表头
	sEnList = oSheet.row_values(iTop+1)     #英文表头
	sTypeList = oSheet.row_values(iTop+2)   #数据类型
	dRowDict = {}

	#数据类型检查
	sErrMsg = ""
	oMaker = g_Maker.get(sMaker, None)
	if not oMaker:
		sErrMsg += u"解析文件 %s 不存在" % sMaker
	for i in xrange(iLeft, iRight):
		sColName = sCnList[i]
		if not sTypeList:
			sErrMsg += u"第%d列(%s)的数据类型没有填写，请检查\n" % (i+1, sColName)
			continue
		sTypeInfo = sTypeList[i].split(TYPE_SPLIT)
		sCond = ""
		if len(sTypeInfo) == 1:
			sType = sTypeInfo[0]
		else:
			sType = sTypeInfo[0]
			sCond = sTypeInfo[1].lower()
		oChecker = g_Checker.get(sType, None)
		if not oChecker:
			sErrMsg += u"第%d列(%s)的数据类型%s不支持\n" % (i+1, sColName, sType)
			continue
		sColList = oSheet.col_values(i, iTop+3, iBottom)
		for j in xrange(iTop+3, iBottom):
			iRow = j - iTop - 3
			sCol = sColList[iRow]
			if not iRow in dRowDict:
				dRowDict[iRow] = {}
			if sCond == "ukey" and sColList.count(sCol) > 1:
				sErrMsg += u"第%d行的%s重复了\n" % (j+1, sCol)
				continue
			if sCond != "default" and sCol == "":
				sErrMsg += u"第%d行的数据不能为空，请检查\n" % (j+1)
				continue
			try:
				dRowDict[iRow][sEnList[i-iLeft]] = oChecker.Check(sCol, sCond=="default")
			except Exception, err:
				sErrMsg += err.message
				continue

	if sErrMsg:
		print u"错误：", sErrMsg
		raise

	#转换
	data = oMaker.Maker(sTypeList, dRowDict)
	json.dump(data, open(sOutFile, "wb"), indent=4, skipkeys=True, sort_keys=True)
	return True


#加载数据类型检查文件
def LoadChecker(sRootPath):
	global g_Checker
	g_Checker = {}
	OldPath = sys.path[:]
	sys.path = [sRootPath,]
	for item in os.listdir(sRootPath):
		full = os.path.join(sRootPath, item)
		if not os.path.isfile(full):
			continue
		sName, sExt = os.path.splitext(item)
		if sExt.lower() != ".py":
			continue
		g_Checker[sName[5:]] = __import__(sName)
	sys.path = OldPath

#加载表单翻译文件
def LoadMaker(sRootPath):
	global g_Maker
	g_Maker = {}
	OldPath = sys.path[:]
	sys.path = [sRootPath,]
	for item in os.listdir(sRootPath):
		full = os.path.join(sRootPath, item)
		if not os.path.isfile(full):
			continue
		sName, sExt = os.path.splitext(item)
		if sExt.lower() != ".py":
			continue
		g_Maker[sName] = __import__(sName)
	sys.path = OldPath


if __name__ == "__main__":
	LoadChecker("checker")
	LoadMaker("maker")
	Export("xls2json.json")
	os.system("pause")

